<html>
<!-- 2-level master-detail report: just put detail datapacket into master -->
<style>
 {{{defaultstyle}}}
 body {margin: 20px 10px}
 td, th {border: solid #eee 1px; font-size: 12px; padding: 2 2; }
 th {background: #888; color: white; padding: 5px 5px; draggable: true; resize: horizontal; }
 .total td {border: none; border-top: solid #888 1px; font-weight: bold}
 .master {border: none; border-bottom: solid red 1px; font-size: 20px; color: #55c;}
</style>

<report-params>
 <param name="state" caption="State" type="list" template=" and state=:state_value" sql="select distinct state, state from customer"/>
 <param name="company" caption="Company" type="string" template=" and company starting with :company_value"/>
 <param name="manager" caption="Manager" type="list" template=" and  o.empno=:manager_value" sql="select empno, lastname from employee"/>
</report-params>


<h2>Customers / Orders</h2>

<DATAPACKET width="780" style="margin: 20 5 10 0">
 <METADATA>
  <FIELDS>
   <FIELD name="COMPANY" class="master"/>
   <FIELD name="SUMM" class="master" />
 </FIELDS>
 <SQL>
   select  c.custno, c.company, sum(itemstotal) summ
   from customer c join orders o on o.custno=c.custno
   group by 1,2
   order by company
 </SQL>
 </METADATA>

 <DETAIL width="780">
  <METADATA autosort="true">
   <FIELDS>
    <FIELD name="ORDERNO" caption="Order No" total="count(*)" width="10%"/>
    <FIELD name="SALEDATE" caption="Sale Date" width="10%"/>
    <FIELD name="SHIPDATE" caption="Ship Date" width="10%"/>
    <FIELD name="LASTNAME" caption="Employee" width="20%"/>
    <FIELD name="PAYMENTMETHOD" caption="Payment Method" align="center" width="20%"/>
    <FIELD name="ITEMSTOTAL" caption="Total" total="sum"  width="15%"/>
    <FIELD name="AMOUNTPAID" caption="Amount Paid" total="sum"  width="15%" format="# ### ##0.00"/>
   </FIELDS>
   <SQL>
    select o.orderno, cast(saledate as date) saledate, cast(shipdate as date) shipdate,
     paymentmethod, itemstotal, amountpaid, e.lastname
    from orders o
     join employee e on e.empno=o.empno
    where custno=:custno {{manager}}
    order by 1
   </SQL>
  </METADATA>
 </DETAIL>

<template-row custom="true">
 <![CDATA[
 <table {{{tableattributes}}}>
  <tr>
  {{{_text}}}
  </tr>
  </table>
  {{{detail}}}
   <CHART width="800" height="300" >
    <SERIES>
     <S  type="pie" x="ayear" y="summ" ></S>
    </SERIES>
    <sql>
     select extract(year from saledate) ayear, sum(itemstotal) summ
     from orders where custno={{CUSTNO}} group by 1
    </sql>
   </CHART>
  ]]>
 </template-row>
</DATAPACKET>

</html>
